package dao;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import model.GraphResult;
import model.GraphSearchCriteria;
import service.impl.CloseConnections;
import service.impl.Converter;
import enums.DataType;

public class GraphDAO {
	static Connection currentConnection = null;
	static ResultSet rs = null;

	public static List<GraphResult> getGraphResults(GraphSearchCriteria criteria) {
		PreparedStatement statement = null;
		List<GraphResult> graphResults = new ArrayList<GraphResult>();
		try {
			currentConnection = ConnectionManager.getConnection();
			currentConnection.setAutoCommit(false);
			Map<DataType, Object> params = new LinkedHashMap<DataType, Object>();
			String sql = "select r.answer_time, r.answer_day_of_week, r.course_code, u.code as user_code, ";
			sql += "lm.type as method_type, r.study_period ";
			sql += "from result r ";
			sql += "inner join user u on u.id = r.user_id ";
			sql += "inner join learning_method lm on lm.id = r.learning_method_id ";
			if (Converter.isNotEmpty(criteria.getGroup())) {
				sql += "inner join group_member gm on gm.user_id = r.user_id ";
				sql += "inner join user_group ug on gm.group_id = ug.id ";
			}
			sql += "where 1=1 ";

			Date periodFrom = criteria.getPeriodFrom();
			Date periodTo = criteria.getPeriodTo();
			if (periodFrom != null && periodTo != null) {
				sql += "and DATE_FORMAT(r.answer_time,'%Y-%m-%d') >= DATE_FORMAT(?,'%Y-%m-%d') and DATE_FORMAT(r.answer_time,'%Y-%m-%d') <= DATE_FORMAT(?,'%Y-%m-%d') ";
				params.put(DataType.DATE_FROM, periodFrom);
				params.put(DataType.DATE_TO, periodTo);
			} else {
				sql += "and DATE_FORMAT(r.answer_time,'%Y-%m-%d') = DATE_FORMAT(?,'%Y-%m-%d') ";
				params.put(DataType.DATE_FROM, periodFrom == null ? periodTo : periodFrom);
			}
			if (Converter.isNotEmpty(criteria.getCourseCode())) {
				sql += "and upper(r.course_code) = upper(?) ";
				params.put(DataType.COURSE_CODE, criteria.getCourseCode());
			}
			if (Converter.isNotEmpty(criteria.getStudentCode())) {
				sql += "and upper(u.code) = upper(?) ";
				params.put(DataType.STUDENT_CODE, criteria.getStudentCode());
			}
			if (Converter.isNotEmpty(criteria.getGroup())) {
				sql += "and upper(ug.type) = upper(?) ";
				params.put(DataType.GROUP, criteria.getGroup());
			}
			if (criteria.getMethodId() != null) {
				sql += "and r.learning_method_id = ? ";
				params.put(DataType.LONG, criteria.getMethodId());
			}
			statement = currentConnection.prepareStatement(sql);
			int idx = 1;
			for (DataType key : params.keySet()) {
				Object object = params.get(key);
				System.out.println(key + " " + idx + " " + object);
				switch (key) {
					case DATE_TO :
					case DATE_FROM :
						statement.setDate(idx, Converter.getSqlDate((Date) object));
						idx++;
						break;
					case LONG :
						statement.setLong(idx, (Long) object);
						idx++;
						break;
					default :
						statement.setString(idx, (String) object);
						idx++;
						break;
				}
			}
			rs = statement.executeQuery();
			while (rs.next()) {
				GraphResult graphResult = new GraphResult();
				graphResult.setAnswerTime(rs.getDate("answer_time"));
				graphResult.setAnswerDayOfWeek(rs.getInt("answer_day_of_week"));
				graphResult.setCourseCode(rs.getString("course_code"));
				graphResult.setUserCode(rs.getString("user_code"));
				graphResult.setMethodType(rs.getString("method_type"));
				graphResult.setStudyPeriod(rs.getDouble("study_period"));
				graphResults.add(graphResult);
			}

		} catch (BatchUpdateException s) {
			System.out.println("SQL statement is not executed!" + s);
		} catch (Exception e) {
			System.out.println("Exception in connection!: " + e);
		} finally {
			CloseConnections.closeAll(rs, statement, currentConnection);
		}
		return graphResults;
	}
}
